﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：王广浩 
    //
    // 文件名：DAL_Hospital.cs
    // 文件功能描述：医疗机构数据访问逻辑
    // 创建标识：王广浩2010-05-27
   //描        述： 这是医疗机构对2010内蒙增补目录 进行填写本医疗机构的采购价格 临时使用。
 *                       不涉及交易部份
 *                       如删除请 删除相关页面 及数据中的表及视图 如下：
 *                       表 ：TB_HospitalLoginPriceGoods TB_HospitalLoginPrice
 *                       视图：View_HospitalLoginPrice
 *                       Model:View/HospitalLoginPricegoods.cs
 *                                  View/HospitalLoginPriceGoods.cs

 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.DrugTrade.Model;
using HZ.Common;
using HZ.Data;
using System.Data;
using System.Data.SqlClient;

namespace HZ.DrugTrade.DAL
{
    public class DAL_HospitalLoginPriceGoods
    {
        /// <summary>
        /// 获得需要医疗机构填报列表
        /// </summary>
        /// <param name="where">列表条件</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pagesize">每页大小</param>
        /// <param name="totalrecord">总页数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods> GetHospitalLoginPriceGoods(HZ.DrugTrade.Model.Query.HospitalLoginPriceGoods where, int currentPage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format(" SELECT * FROM TB_HospitalLoginPriceGoods Where 1=1 {0}", strwhere);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentPage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods>(dt);
        }

        /// <summary>
        /// 获得医疗机构填报列表
        /// </summary>
        /// <param name="where">列表条件</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pagesize">每页大小</param>
        /// <param name="totalrecord">总页数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods> GetGoodsByWhere(HZ.DrugTrade.Model.Query.HospitalLoginPriceGoods where, int currentPage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format(" SELECT * FROM View_HospitalLoginPrice  Where 1=1 {0}", strwhere);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentPage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods>(dt);
        }

        /// <summary>
        /// 汇总所有医疗机构打的最低价和最高价
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods> getGoodsToTalByWhere(HZ.DrugTrade.Model.Query.HospitalLoginPriceGoods where, int currentpage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format(@" SELECT A.*,B.* FROM TB_HospitalLoginPriceGoods A LEFT JOIN TB_HospitalLoginPriceTotal  B ON A.GOODSID=B.GOODSIDS   Where 1=1 {0}", strwhere);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalLoginPriceGoods>(dt);
        }

        /// <summary>
        /// 计算医疗机构价格
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static int CalHospitalLoginPrice()
        {
            int intFlag = 0;
         
            var db = HZ.Data.DbHelperFactory.Create();
            string strAdd = null;
            try
            {
                db.BeginTrans();
                string strSql = @"begin 
                                    delete from TB_HospitalLoginPriceTotal;

                                    insert into TB_HospitalLoginPriceTotal
                                    select a.GoodsId GOODSIDS,--CountProduces,
                                    a.price as minPrice,a.hospitalname as minHospitalName,
                                    b.price as maxprice,b.hospitalname as maxHospitalName
                                    --Into TB_HospitalLoginPriceTotal
                                    from
                                    (Select GoodsID,HospitalName,Price,CountProduces,Row_number() over(partition by GoodsID order by price asc) as num1 from TB_HospitalLoginPrice) a 
                                    join 
                                    (Select GoodsID,HospitalName,Price,Row_number() over(partition by GoodsID order by price desc) as num1 from TB_HospitalLoginPrice) b
                                    on a.goodsid=b.goodsid and a.num1=1 and b.num1=1;
                                    end;
                                    ";
                //db.ExecuteNonQuery(CommandType.StoredProcedure, "usb_HospitalLoginPriceTotal", null);
                db.ExecuteNonQuery(strSql);
                db.CommitTrans();
                intFlag = 1;
            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug(strAdd, ee.Message, ee);
                db.RollTrans();
                intFlag = 0;
                throw;
            }
            return intFlag;
        }
        public static int UpdateGoodsLoginPrice(Model.View.HospitalLoginPriceGoods where)
        {
            int intFlag = 0;
          
            var db = HZ.Data.DbHelperFactory.Create();
            string strAdd = null;
            try
            {
                db.BeginTrans();
//                strAdd = @" 
//                          IF EXISTS(SELECT * FROM TB_HospitalLoginPrice WHERE GoodsID=@GoodsID and HospitalID=@HospitalID)
//                          BEGIN
//                          IF (@price>0  and @count>0 )         --填负或者为0 都不保存
//                               Update TB_HospitalLoginPrice Set Price=@price , CountProduces=@count where GoodsID=@GoodsID and HospitalID=@HospitalID
//                          Else
//                               Delete FROM TB_HospitalLoginPrice WHERE GoodsID=@GoodsID and HospitalID=@HospitalID 
//                          END
//                          ELSE
//                          BEGIN
//                              Insert Into TB_HospitalLoginPrice(GoodsID,HospitalID,HospitalName,AreaID,price,CountProduces) values(@goodsID,@HospitalID,@HospitalName,@AreaID,@Price,@count)
//                          END";




                #region 
                string strCheck = @" 
                          SELECT count(1) num FROM TB_HospitalLoginPrice WHERE GoodsID=@in_GoodsID and HospitalID=@in_HospitalID ";

                List<string> parasnames1 = new List<string>();
                List<object> values1 = new List<object>();

                parasnames1.Add("@in_GoodsID");
                values1.Add(where.GoodsID);

                parasnames1.Add("@in_HospitalID");
                values1.Add(where.HospitalID);

                DataTable dt = db.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                int num = int.Parse(dt.Rows[0]["num"].ToString()); 
                #endregion

                if (num>0)
                {


                    if (where.Price>0 && where.CountProduces>0)
                    {
                        string strUpdateHospitalLoginPrice = @"Update TB_HospitalLoginPrice Set Price=@in_Price , CountProduces=@in_Count where GoodsID=@in_GoodsID and HospitalID=@in_HospitalID";
                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_GoodsID");
                        values2.Add(where.GoodsID);

                        parasnames2.Add("@in_HospitalID");
                        values2.Add(where.HospitalID);

                        parasnames2.Add("@in_Price");
                        values2.Add(where.Price);

                        parasnames2.Add("@in_Count");
                        values2.Add(where.CountProduces);

                        db.ExecuteNonQuery(CommandType.Text, strUpdateHospitalLoginPrice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    }
                    else
                    {
                        string strDelHospitalLoginPrice = @"Delete FROM TB_HospitalLoginPrice WHERE GoodsID=@in_GoodsID and HospitalID=@in_HospitalID";
                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_GoodsID");
                        values2.Add(where.GoodsID);

                        parasnames2.Add("@in_HospitalID");
                        values2.Add(where.HospitalID);

                        db.ExecuteNonQuery(CommandType.Text, strDelHospitalLoginPrice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    }


                }
                else
                {
                    string strInsertHospitalLoginPrice = @" Insert Into TB_HospitalLoginPrice
                                (GoodsID,HospitalID,HospitalName,AreaID,price,CountProduces)
                        values(@in_GoodsID,@in_HospitalID,@in_HospitalName,@in_AreaID,@in_Price,@in_Count)";
                    List<string> parasnames2 = new List<string>();
                    List<object> values2 = new List<object>();

                    parasnames2.Add("@in_GoodsID");
                    values2.Add(where.GoodsID);

                    parasnames2.Add("@in_HospitalID");
                    values2.Add(where.HospitalID);

                    parasnames2.Add("@in_Price");
                    values2.Add(where.Price);

                    parasnames2.Add("@in_Count");
                    values2.Add(where.CountProduces);

                    parasnames2.Add("@in_HospitalName");
                    values2.Add(where.HospitalName);


                    parasnames2.Add("@in_AreaID");
                    values2.Add(where.AreaID);

                    db.ExecuteNonQuery(CommandType.Text, strInsertHospitalLoginPrice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());

                }
             
                db.CommitTrans();
                intFlag = 1;
            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug(strAdd, ee.Message, ee);
                db.RollTrans();
                intFlag = 0;
                throw;
            }
            return intFlag;
        }
       
        /// <summary>
        /// 判断专家是否已给企业打分
        /// </summary>
        /// <param name="AccountId">账户注册号</param>
        /// <returns></returns>
        public static long CheckExist(string Hospital, string GoodsID)
        {
            int intTemp;
            Model.Query.HospitalLoginPriceGoods Q = new Model.Query.HospitalLoginPriceGoods();
            Q.HospitalID = Hospital;
            Q.GoodsID = GoodsID;
            IList<Model.View.HospitalLoginPriceGoods> lst = GetGoodsByWhere(Q, 1, 0, out intTemp);
            if (lst.Count > 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 开启县级以上医疗机构填报权限[这是一次性使用功能 。所以就没有做到权限控制里单独做了一个功能]
        /// </summary>
        /// <returns></returns>
        public static bool OpenCountryOver()
        {
            bool boolFlag ;
        
            var db = HZ.Data.DbHelperFactory.Create();

            string strSQL = null;
            try
            {
                db.BeginTrans();


                #region
                string strCheck = @" 
                          SELECT count(1) num From TB_OperatingRule where OperatingRuleID=1900 ";

                DataTable dt = db.ExecuteTable(strCheck);

                int num = int.Parse(dt.Rows[0]["num"].ToString());
                #endregion

                if (num>0)
                {
                    string strInsertUserRule = @"Insert Into TB_UserRule 
                            Select HospitalID,1900,1 From TB_HospitalInfo 
                            Where   not exists(  select HospitalID from TB_UserRule where OperatingRuleID=1900 
                            and TB_HospitalInfo.Hospitalid=TB_UserRule.UserID) and  CountyOver=1";
                    db.ExecuteNonQuery(strInsertUserRule);

                }
//                strSQL = @"
//                               
//                            IF Not Exists(Select * From TB_OperatingRule where OperatingRuleID=1900)  --没有权限规则
//                                     Select  0;
//                            Else
//                               Begin
//                                Insert Into TB_UserRule Select HospitalID,1900,1 From TB_HospitalInfo Where   not exists(  select HospitalID from TB_UserRule where OperatingRuleID=1900 and TB_HospitalInfo.Hospitalid=TB_UserRule.UserID) and  CountyOver=1 
//                                  select 1
//                               End
//                                  ";
//                db.ExecuteNonQuery(strSQL);
                db.CommitTrans();
                boolFlag = true; ;
            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug(strSQL, ee.Message, ee);
                db.RollTrans();
                boolFlag = false;
                throw;
            }
            return boolFlag;
          
        }

        /// <summary>
        /// 开启县级以上医疗机构填报权限[这是一次性使用功能 。所以就没有做到权限控制里单独做了一个功能]
        /// </summary>
        /// <returns></returns>
        public static bool CloseCountryOver()
        {
            bool boolFlag ;
          
            var db = HZ.Data.DbHelperFactory.Create();
            string strSQL = null;
            try
            {
                db.BeginTrans();

                strSQL = @"Delete From  TB_UserRule Where  OperatingRuleID=1900 ";
                db.ExecuteNonQuery(strSQL);
                db.CommitTrans();
                boolFlag = true;
            }
            catch (Exception ee)
            {

                HZ.Common.log4netManager.LogDebug(strSQL, ee.Message, ee);
                db.RollTrans();
                boolFlag = false;
                throw;
            }
            return boolFlag;
            
        }

        /// <summary>
        /// 2010.9.15增加功能
        /// 根据流水号和登录的医疗机构ID号码，清空填写了报价和购买数量的数据，
        /// 同时把清空的数据的字段显示在未确定的报价的页面中进行重新报价和购买数量
        /// </summary>
        /// <param name="HospitalID"></param>
        /// <param name="GoodsID"></param>
        /// <returns></returns>
        public static int DeletePriceCount(string HospitalID, int GoodsID)
        {
            int i;
         
            var h = HZ.Data.DbHelperFactory.Create();
            string sql = null;
            try
            {
                sql = string.Format("update tb_hospitalloginprice set countProduces=0,price='0' where  goodsid={0} and hospitalid='{1}' ", GoodsID, HospitalID);
                i = h.ExecuteNonQuery(sql);  
            }
            catch (Exception  e)
            {

                HZ.Common.log4netManager.LogDebug(sql, e.Message, e);
                throw;
            }
            return i;
        }
    }
}
